﻿/*
	This SQL provides information to populate the parameters for functions
*/

SELECT     INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_CATALOG AS TABLE_CATALOG, INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_SCHEMA AS TABLE_SCHEMA, 
                      INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_NAME AS TABLE_NAME, INFORMATION_SCHEMA.PARAMETERS.ORDINAL_POSITION, 
                      INFORMATION_SCHEMA.PARAMETERS.PARAMETER_MODE, INFORMATION_SCHEMA.PARAMETERS.IS_RESULT, 
                      INFORMATION_SCHEMA.PARAMETERS.AS_LOCATOR, INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME AS COLUMN_NAME, 
                      INFORMATION_SCHEMA.PARAMETERS.DATA_TYPE, INFORMATION_SCHEMA.PARAMETERS.CHARACTER_MAXIMUM_LENGTH, 
                      INFORMATION_SCHEMA.PARAMETERS.CHARACTER_OCTET_LENGTH, INFORMATION_SCHEMA.PARAMETERS.COLLATION_CATALOG, 
                      INFORMATION_SCHEMA.PARAMETERS.COLLATION_SCHEMA, INFORMATION_SCHEMA.PARAMETERS.COLLATION_NAME, 
                      INFORMATION_SCHEMA.PARAMETERS.CHARACTER_SET_CATALOG, INFORMATION_SCHEMA.PARAMETERS.CHARACTER_SET_SCHEMA, 
                      INFORMATION_SCHEMA.PARAMETERS.CHARACTER_SET_NAME, INFORMATION_SCHEMA.PARAMETERS.NUMERIC_PRECISION, 
                      INFORMATION_SCHEMA.PARAMETERS.NUMERIC_PRECISION_RADIX, INFORMATION_SCHEMA.PARAMETERS.NUMERIC_SCALE, 
                      INFORMATION_SCHEMA.PARAMETERS.DATETIME_PRECISION, INFORMATION_SCHEMA.PARAMETERS.INTERVAL_TYPE, 
                      INFORMATION_SCHEMA.PARAMETERS.INTERVAL_PRECISION, INFORMATION_SCHEMA.PARAMETERS.USER_DEFINED_TYPE_CATALOG, 
                      INFORMATION_SCHEMA.PARAMETERS.USER_DEFINED_TYPE_SCHEMA, INFORMATION_SCHEMA.PARAMETERS.USER_DEFINED_TYPE_NAME, 
                      INFORMATION_SCHEMA.PARAMETERS.SCOPE_CATALOG, INFORMATION_SCHEMA.PARAMETERS.SCOPE_SCHEMA, 
                      INFORMATION_SCHEMA.PARAMETERS.SCOPE_NAME, '' AS COLUMN_DEFAULT
FROM         INFORMATION_SCHEMA.PARAMETERS INNER JOIN
                      INFORMATION_SCHEMA.ROUTINES ON INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_CATALOG = INFORMATION_SCHEMA.ROUTINES.ROUTINE_CATALOG AND 
                      INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_SCHEMA = INFORMATION_SCHEMA.ROUTINES.ROUTINE_SCHEMA AND 
                      INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_NAME = INFORMATION_SCHEMA.ROUTINES.ROUTINE_NAME
WHERE     (INFORMATION_SCHEMA.ROUTINES.DATA_TYPE = N'TABLE' OR INFORMATION_SCHEMA.ROUTINES.DATA_TYPE IS NULL)